﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;
using System.Data;
using System.Data.SqlTypes;

namespace Trying1.Classes
{
    class GuestDBManager
    {
        public static ArrayList GetAll()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Guest";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Guest m = new Guest();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.noOfGuest = (int)dr["noOfGuest"];
                    m.noOfChildren = (int)dr["noOfChildren"];
                    m.phoneNum = (Int64)dr["phoneNum"];
                    m.emailAddress = (string)dr["emailAddress"];
                    m.streetAddress = (string)dr["streetAddress"];
                    m.houseNo = (string)dr["houseNo"];
                    m.postalCode = (string)dr["postalCode"];
                    m.country = (string)dr["country"];
                    m.paymentType = (string)dr["paymentType"];
                    m.ccNo = (string)dr["ccNo"];
                    m.expDate = (DateTime)dr["expDate"];
                    m.ccName = (string)dr["ccName"];
                    m.roomNum = (int)dr["roomNum"];
                   
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static Guest GetGuestByfirstName(string firstName)
        {
            Guest m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE firstName=@firstName";
                comm.Parameters.AddWithValue("@firstName", firstName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Guest();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.noOfGuest = (int)dr["noOfGuest"];
                    m.noOfChildren = (int)dr["noOfChildren"];
                    m.phoneNum = (Int64)dr["phoneNum"];
                    m.emailAddress = (string)dr["emailAddress"];
                    m.streetAddress = (string)dr["streetAddress"];
                    m.houseNo = (string)dr["houseNo"];
                    m.postalCode = (string)dr["postalCode"];
                    m.country = (string)dr["country"];
                    m.paymentType = (string)dr["paymentType"];
                    m.ccNo = (string)dr["ccNo"];
                    m.expDate = (DateTime)dr["expDate"];
                    m.ccName = (string)dr["ccName"];
                    m.roomNum = (int)dr["roomNum"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
       
        public static Guest GetInsertedGuestReservation(string firstName, string lastName, int roomNum)
        {
            Guest m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE firstName=@firstName AND lastName=@lastName AND roomNum=@roomNum";
                
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Guest();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.noOfGuest = (int)dr["noOfGuest"];
                    m.noOfChildren = (int)dr["noOfChildren"];
                    m.phoneNum = (Int64)dr["phoneNum"];
                    m.emailAddress = (string)dr["emailAddress"];
                    m.streetAddress = (string)dr["streetAddress"];
                    m.houseNo = (string)dr["houseNo"];
                    m.postalCode = (string)dr["postalCode"];
                    m.country = (string)dr["country"];
                    m.paymentType = (string)dr["paymentType"];
                    m.ccNo = (string)dr["ccNo"];
                    m.expDate = (DateTime)dr["expDate"];
                    m.ccName = (string)dr["ccName"];
                    m.roomNum = (int)dr["roomNum"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static ArrayList FindAllGuest()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT guestNo,firstName,lastName,roomNum from Guest";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Guest m = new Guest();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.roomNum = (int)dr["roomNum"];

                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        
        public static int InsertGuest(Guest m)
        {
            int rowsinserted = 0;
            
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Guest(firstName,lastName,noOfGuest,noOfChildren,phoneNum,emailAddress,streetAddress,houseNo,postalCode,country,paymentType,ccNo,expDate,ccName,roomNum)" +
                    " VALUES (@firstName,@lastName,@noOfGuest,@noOfChildren,@phoneNum,@emailAddress,@streetAddress,@houseNo,@postalCode,@country,@paymentType,@ccNo,@expDate,@ccName,@roomNum) ";
                comm.Parameters.AddWithValue("@firstName", m.firstName);
                comm.Parameters.AddWithValue("@lastName", m.lastName);
                comm.Parameters.AddWithValue("@noOfGuest", m.noOfGuest);
                comm.Parameters.AddWithValue("@noOfChildren", m.noOfChildren);
                comm.Parameters.AddWithValue("@phoneNum", m.phoneNum);
                comm.Parameters.AddWithValue("@emailAddress", m.emailAddress);
                comm.Parameters.AddWithValue("@streetAddress", m.streetAddress);
                comm.Parameters.AddWithValue("@houseNo", m.houseNo);
                comm.Parameters.AddWithValue("@postalCode", m.postalCode);
                comm.Parameters.AddWithValue("@country", m.country);
                comm.Parameters.AddWithValue("@paymentType", m.paymentType);
                comm.Parameters.AddWithValue("@ccNo", m.ccNo);
                comm.Parameters.AddWithValue("@expDate", m.expDate);
                comm.Parameters.AddWithValue("@ccName", m.ccName);
                comm.Parameters.AddWithValue("@roomNum", m.roomNum);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateGuest(Guest m)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Guest SET firstName=@firstName,lastName=@lastName,noOfGuest=@noOfGuest,noOfChildren=@noOfChildren,phoneNum=@phoneNum,emailAddress=@emailAddress,streetAddress=@streetAddress,houseNo=@houseNo,postalCode=@postalCode,country=@country,paymentType=@paymentType,ccNo=@ccNo,expDate=@expDate,ccName=@ccName, roomNum=@roomNum where guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", m.guestNo);
                comm.Parameters.AddWithValue("@firstName", m.firstName);
                comm.Parameters.AddWithValue("@lastName", m.lastName);
                comm.Parameters.AddWithValue("@noOfGuest", m.noOfGuest);
                comm.Parameters.AddWithValue("@noOfChildren", m.noOfChildren);
                comm.Parameters.AddWithValue("@phoneNum", m.phoneNum);
                comm.Parameters.AddWithValue("@streetAddress", m.streetAddress);
                comm.Parameters.AddWithValue("@emailAddress", m.emailAddress);
                comm.Parameters.AddWithValue("@houseNo", m.houseNo);
                comm.Parameters.AddWithValue("@postalCode", m.postalCode);
                comm.Parameters.AddWithValue("@country", m.country);
                comm.Parameters.AddWithValue("@paymentType", m.paymentType);
                comm.Parameters.AddWithValue("@ccNo", m.ccNo);
                comm.Parameters.AddWithValue("@expDate", m.expDate);
                comm.Parameters.AddWithValue("@ccName", m.ccName);
                comm.Parameters.AddWithValue("@roomNum", m.roomNum);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteGuest(int guestNo)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from Guest where guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        public static Guest GetGuestByguestNo(int guestNo)
        {
            Guest m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Guest();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.noOfGuest = (int)dr["noOfGuest"];
                    m.noOfChildren = (int)dr["noOfChildren"];
                    m.phoneNum = (Int64)dr["phoneNum"];
                    m.emailAddress = (string)dr["emailAddress"];
                    m.streetAddress = (string)dr["streetAddress"];
                    m.houseNo = (string)dr["houseNo"];
                    m.postalCode = (string)dr["postalCode"];
                    m.country = (string)dr["country"];
                    m.paymentType = (string)dr["paymentType"];
                    m.ccNo = (string)dr["ccNo"];
                    m.expDate = (DateTime)dr["expDate"];
                    m.ccName = (string)dr["ccName"];
                    m.roomNum = (int)dr["roomNum"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
    }

}
